WEBVTT 00:00.980 --> 00:01.480 All right. 00:01.490 --> 00:08.510 We are quoting now the S&P 500 the trend triangle and first of all we import Pena's name pi and seaborne 00:08.510 --> 00:10.210 and also Matt Platt lip. 00:10.220 --> 00:17.690 And we will use the Seabourn style and then we are working with the S&P 500 CSP file. 00:17.730 --> 00:23.310 So we import the file and of course we set the date column as being the day time index. 00:23.510 --> 00:27.290 And we only use them the column date and close. 00:27.620 --> 00:31.280 So we are working with the closing prices. 00:31.280 --> 00:32.710 And again he as a side note. 00:32.720 --> 00:39.230 So we only capture here the price return and not the total return because we do not include here the 00:39.230 --> 00:40.140 dividends. 00:40.160 --> 00:43.400 So let's import here and let's have a look. 00:43.430 --> 00:46.000 So this is nothing new. 00:46.040 --> 00:50.460 So our dataset starts here in the year nineteen hundred seventy. 00:50.930 --> 00:57.380 And for our purposes we are only interested that in the last the 30 year period. 00:57.380 --> 01:04.820 So from nineteen hundred eighty nine today to two thousand and eighteen. 01:04.880 --> 01:11.750 So now here our data frame starts at the very last day of the year nineteen hundred eighty eight and 01:11.750 --> 01:18.110 actually follow our return triangle we want to work with add new price data and therefore we will sample 01:18.140 --> 01:22.250 the S&P 500 the data frame to endure. 01:22.430 --> 01:25.960 And we take the very last trading price of a particular year. 01:25.970 --> 01:31.790 So let's throw this here and uh we save the IRI sample data frame and the variable annual. 01:33.200 --> 01:36.650 So these are actually the annual prices. 01:36.650 --> 01:39.570 So in nineteen hundred eighty eight eighty nine and so on. 01:39.950 --> 01:43.160 And then as a next step we create annual lucky returns. 01:43.610 --> 01:50.900 So we create the column return and we take the close column and divide each element in the cross column 01:51.290 --> 01:56.660 by the immediately preceding price and then we take the logarithm. 01:56.690 --> 02:04.570 So let's do this here and we already know that we produce at least one and a value. 02:04.580 --> 02:10.040 So apparently there might not be any return for the year nineteen hundred eighty eight 02:13.130 --> 02:15.020 so let's have a look here. 02:15.050 --> 02:18.900 So in our data frame starts in the year nineteen hundred eighty nine. 02:19.010 --> 02:25.150 And here we have the very last trading price and here we have the end you are lucky returns. 02:25.160 --> 02:31.730 So for example the annual lock return for the year nineteen hundred eighty nine was a 24 percent and 02:31.730 --> 02:38.080 in total we have annual returns from nineteen hundred eighty nine to two thousand and eighteen. 02:38.120 --> 02:43.530 So here we have a negative lock return of minus seven point two percent. 02:43.610 --> 02:51.020 So in total we have a period of 30 years but we can also make this more explicit by calling here the 02:51.020 --> 02:55.370 index and the calculate actually the size of our index. 02:55.700 --> 03:00.720 And uh we actually save the size and the variable yes so it should be 30. 03:00.750 --> 03:08.270 So 30 years and then we want to create the rolling statistics for investment periods from one year to 03:08.270 --> 03:09.390 30 years. 03:09.560 --> 03:11.990 And as you might recall in the last video. 03:12.380 --> 03:19.280 So the 30 year period started on the left hand side of our chart and the one year period on the right 03:19.280 --> 03:26.930 hand side and therefore first of all we created a list with a list comprehension and the list should 03:26.930 --> 03:32.220 include the integer starting from 30 until 1 inclusive. 03:32.420 --> 03:40.280 And we can do the CSO year for year in range uh 30 to zero exclusive with the steps left minus 1. 03:40.280 --> 03:43.400 So let's have a look at the list of Windows. 03:43.400 --> 03:48.020 So here we have the integers from 38 uh 1. 03:49.020 --> 03:53.360 And now we will create the 30 columns for our annual data frame. 03:53.720 --> 04:02.530 So for each period starting with 30 years until one year we are actually creating a column so for year 04:02.560 --> 04:09.630 and Windows starting here with 30 years until one year we create the column for the annual data frame 04:09.720 --> 04:17.590 30 years twenty nine years until one year and we actually take the annual lock return column and calculate 04:17.700 --> 04:19.790 uh the rolling mean. 04:19.800 --> 04:26.620 So this is a for loop and in the very first iteration we are starting with uh 30 here. 04:26.670 --> 04:28.650 So an investment period of 30. 04:29.190 --> 04:36.300 And we create the column 30 years then we are calculating the rolling mean return with a rolling window 04:36.300 --> 04:38.130 of 30 years. 04:38.160 --> 04:39.300 So let's have a look here. 04:39.300 --> 04:41.180 So let's uh execute here. 04:41.190 --> 04:45.470 The for loop and let's have a look at our data frame and Uralla. 04:46.350 --> 04:51.000 So here we can see that we successfully EDIT Actually 30 columns. 04:51.300 --> 04:56.670 So here we have the close column the return column and then give you a further rolling mean for the 04:56.670 --> 04:59.310 investment period of 30 years. 04:59.310 --> 05:03.330 So no surprise that we have uh any end values here. 05:03.330 --> 05:07.700 And as we in total consider only the period starting from nineteen hundred eighty nine. 05:07.770 --> 05:11.820 So we have you in the column 30 years only one data point. 05:11.910 --> 05:18.570 So here for the period from nineteen hundred eighty nine to two thousand and eighteen then we have a 05:18.570 --> 05:21.360 fun investment period of twenty nine years. 05:21.510 --> 05:26.030 We have two data points or two or rolling statistics. 05:26.340 --> 05:34.270 And if we go to the right here then here we have um the one year rolling average return. 05:34.320 --> 05:38.520 So these are actually the respective lock returns and the particular year. 05:38.520 --> 05:46.140 So starting from nineteen hundred eighty nine to two thousand and eighteen and we are actually almost 05:46.140 --> 05:46.820 finished here. 05:46.860 --> 05:52.770 So the only thing we have to do here we have to delete the columns close and return. 05:52.770 --> 05:59.580 So we are happy here with the 30 columns here both of the rolling statistics and therefore we drop the 05:59.580 --> 06:04.900 columns close and return and we save the new data frame and the rabbit triangle. 06:04.900 --> 06:06.330 So that's with us. 06:06.690 --> 06:13.220 And that's again never look so here we have on the left hand side on the y axis. 06:13.280 --> 06:18.950 The final year of investment and on the x axis here the investment periods. 06:19.370 --> 06:26.180 So we have successfully rearranged here and aggregated our data and now we can visualize our data and 06:26.180 --> 06:28.650 create a return triangle. 06:29.360 --> 06:33.530 And uh we can do this year with a seaborne heat map. 06:33.530 --> 06:38.270 So first of all we defined that you want to have a fixed size of 50 40. 06:38.270 --> 06:42.630 So this is a quite large graph here actually. 06:42.710 --> 06:50.240 Then we increase the funds go to one point eight and then finally we pass our triangular data frame 06:50.240 --> 06:52.130 to the S.A. heat map. 06:52.130 --> 06:54.620 So let's run the other side and let's have a look 07:01.110 --> 07:07.280 so here we have our return triangle and this looks quite familiar actually for us. 07:07.380 --> 07:15.380 And uh let's go also here to the decoding details so we can actually see in each cell the average are 07:15.390 --> 07:20.970 the mean lock return and by passing through to the end of parameter we are actually adding here the 07:20.970 --> 07:21.890 numbers. 07:21.930 --> 07:26.220 And then next we have the format parameter and here we can define that. 07:26.220 --> 07:34.260 Our numbers should be in percent with the one decimal and as color map we have to find your red yellow 07:34.260 --> 07:34.860 green. 07:35.280 --> 07:42.800 So we can see here that the negative returns are red and the positive returns are green. 07:43.020 --> 07:46.980 And actually uh zero returns are kind of low. 07:48.030 --> 07:53.520 And with the women and we max we can also define the scale of our color map. 07:53.730 --> 07:56.180 So starting with minus 10 percent. 07:56.210 --> 07:59.050 So minus 10 percent is the dark red. 07:59.400 --> 08:08.070 And actually plus 15 percent is dark green and the center should be actually at 0 percent. 08:08.070 --> 08:14.310 So here we have a yellow and actually here we have another met public function party the tick parents 08:14.910 --> 08:21.510 and we define here that for the y axis we set a label right to true. 08:21.510 --> 08:26.890 So we can see here that we have few on the left hand side and also on the right hand side here. 08:26.910 --> 08:32.340 The y axis labels and the free pass here for it to the label right 08:42.280 --> 08:47.340 then we only f on the left hand side here the labels but not on the right hand side. 08:47.410 --> 08:53.070 And it might be more comfortable to have these labels also on the right hand side and therefore we pass 08:53.070 --> 09:05.110 the a true. 09:05.160 --> 09:10.020 So this is our final return triangle for the S&P 500 index. 09:10.270 --> 09:12.140 And as we have seen here. 09:12.150 --> 09:14.930 So we only need the some few lines of code. 09:14.940 --> 09:19.170 So this is pretty amazing and I hope to see you also in the next video by.